package com.jdbc.dbutils.crud;

import com.jdbc.entity.Student;
import com.jdbc.utils.JDBCUtil;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;


import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author wzm
 * @create 2023-02-23 14:23
 */
//用来测试DBUtils
public class DBUtilsTest {

    //新增学生
    @Test
    public void addStu() throws SQLException {
        //conn
        Connection conn = JDBCUtil.getConn();
        //sql
        String sql = "insert into student values(?,?,?,?,?,?)";

        //用于执行功能的类
        QueryRunner queryRunner = new QueryRunner();
        //调用增删改通用的方法  连接 sql  参数(？)
        int update = queryRunner.update(conn, sql, null, "马超", "男", 38, "昆明", 1);
        System.out.println("受影响行数 update = " + update);

        //关闭资源
        DbUtils.closeQuietly(conn);
    }

    //删除学生
    @Test
    public void deleteStu() throws SQLException {
        Connection conn = JDBCUtil.getConn();
        String sql = "delete from student where id = ?";

        QueryRunner runner = new QueryRunner();
        int update = runner.update(conn, sql, 7);
        System.out.println("删除的数据 = " + update);
        DbUtils.closeQuietly(conn);
    }

    //修改学生信息
    @Test
    public void updateStu() throws SQLException {
        Connection conn = JDBCUtil.getConn();
        String sql = "update student set stu_name = ?,stu_sex = ?,age = ?, address = ?,status = ? where id = ? ";
        QueryRunner runner = new QueryRunner();
        int update = runner.update(conn, sql, "周润发", "男", 65, "香港", 0, 5);
        System.out.println("修改的数据条数 = " + update);
        DbUtils.closeQuietly(conn);
    }


    //查询一条记录 单个对象
    @Test
    public void selectStuById() throws SQLException {
        Connection conn = JDBCUtil.getConn();
        String sql = "select id,stu_name stuName,stu_sex stuSex,age,address,status from student  where id = ?";

        QueryRunner runner = new QueryRunner();

        //创建一个处理单个对象的结果集处理器
        BeanHandler<Student> beanHandler = new BeanHandler<>(Student.class);

        //参数 连接  sql 用于处理查询后的结果集的类 参数
        Student student = runner.query(conn, sql, beanHandler, 5);
        System.out.println("student = " + student);
    }

    //查询多条记录 集合
    @Test
    public void selectStuByAll() throws SQLException {
        Connection conn = JDBCUtil.getConn();
        String sql = "select id,stu_name stuName,stu_sex stuSex,age,address,status from student where id < ?";

        //创建处理结果集的处理器
        BeanListHandler<Student> beanListHandler = new BeanListHandler<>(Student.class);

        QueryRunner runner = new QueryRunner();
        List<Student> studentList = runner.query(conn, sql, beanListHandler, 10);
        System.out.println("studentList = " + studentList);
    }

    //查询单个值  如 max min sum avg count等聚合函数的查询
    @Test
    public void selectByValue() throws SQLException {
        Connection conn = JDBCUtil.getConn();
//        String sql = "select count(*) from student";
        String sql = "select max(age) from student";

        QueryRunner runner = new QueryRunner();

        //创建查询单个值的结果集处理器
        ScalarHandler handler =new ScalarHandler();

        int o =(int) runner.query(conn, sql, handler);
        System.out.println("o = " + o);
    }


    //自己处理结果集 自定义的ResultHandler
    @Test
    public void selectByCustom() throws SQLException {
        Connection conn = JDBCUtil.getConn();
        String sql = "select * from student where id = ?";
        QueryRunner runner = new QueryRunner();

        //自定义结果集处理器  随意封装需要的内容而不是固定的  接口的匿名子类的实现类
        /*ResultSetHandler<Student> handler = new ResultSetHandler<Student>() {
            //在查询时封装就是调用的这个方法  resultSet就是查询时的源数据
            @Override
            public Student handle(ResultSet resultSet) throws SQLException {
                //根据需求自己定义对象的处理方式 最后返回对象
                //return new Student(1,"我是自定的学生","男",5,"昆明",1);
                if(resultSet.next()){
                    int id = resultSet.getInt("id");
                    String stu_name = resultSet.getString("stu_name");
                    String stu_sex = resultSet.getString("stu_sex");
                    int age = resultSet.getInt("age");
                    String address = resultSet.getString("address");
                    int status = resultSet.getInt("status");
                    return new Student(id,stu_name,stu_sex,age,address,status);
                }

                return null;


            }
        };
*/

        //先用子类实现结果集处理器 然后使用子类来作为结果集处理器(推荐)
        CustomHandler handler = new CustomHandler();


        Student query = runner.query(conn, sql, handler, 5);
        System.out.println("query = " + query);
    }


}
